﻿using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System;
using LumenWorks.Framework.IO.Csv;

//@author: Lénia Andrade nº 5607

namespace LP_C_Sharp_2011_2012
{
    class CreateDatabase
    {

        //This method creates the database

        public void createDB()
        {
            SQLiteConnection connection = new SQLiteConnection("Data Source=Rebides.db");
            connection.Open();
            SQLiteTransaction transaction = connection.BeginTransaction();
            SQLiteCommand command = connection.CreateCommand();


            for (int x = 2000; x <= 2010; x++)
            {
                command.CommandText = string.Format("DROP TABLE IF EXISTS teachers_information_year_{0}", x);

                command.ExecuteNonQuery();
                
                command.CommandText = string.Format("CREATE TABLE IF NOT EXISTS teachers_information_year_{0}(teacher_code integer, teacher_name text," +
                                                            "last_degree text,course text,category text," +
                                                            "regime text,year integer," +
                                                             "establishment_type text," +
                                                            "establishment_name text)",x);

                command.ExecuteNonQuery();
            }
               
            fillDB(connection, command, transaction);

        }

         
        //This method fills the database with the data contained in csv files

        public void fillDB(SQLiteConnection connection, SQLiteCommand command, SQLiteTransaction transaction)
        {


            for (int t = 0; t <= 10; t++)
            {
                int year = 2000 + t;

                CsvReader fileReader = new CsvReader(
                                     new StreamReader(string.Format(".\\rebcsv\\reb{0}.csv",t)), true);


                command.CommandText = string.Format("INSERT INTO teachers_information_year_{0} (teacher_code, teacher_name,last_degree,course,category,regime,year,establishment_type,establishment_name) VALUES(?,?,?,?,?,?,?,?,?)",year);

                int numFields = 9;
                for (int i = 0; i < numFields; i++)
                {
                    command.Parameters.Add(command.CreateParameter());
                }


                while (fileReader.ReadNextRecord())
                {

                    for (int j = 0; j < numFields; j++)
                    {
                        command.Parameters[j].Value = fileReader[j];
                    }

                    command.ExecuteNonQuery();
                }

            }

            transaction.Commit();

            connection.Close();

        }
    }
}
